﻿using System;
using System.Data;
using System.Data.Common;

/// <summary>
///SQLHelper 的摘要说明
/// </summary>
public class SQLHelper
{
    private DbConnection connection;
    
    public SQLHelper()
    {
        this.connection = DbHelper.CreateConnection();
    }

    public SQLHelper(string connectionString)
    {
        this.connection = DbHelper.CreateConnection(connectionString);
    }

    public SQLHelper(string connectionString, string dbProviderName)
    {
        this.connection = DbHelper.CreateConnection(connectionString, dbProviderName);
    }    

    public int RunSql(string sql, params DbParameter[] ps)
    {
        int num; 
        DbHelper Db = new DbHelper();
        DbCommand cmd = Db.GetSqlStringCommand(sql);

        try
        {
            cmd.Parameters.AddRange(ps);
            cmd.Connection = connection;
            num = Db.ExecuteNonQuery(cmd);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            cmd.Dispose();
        }

        return num;      
    }

    public int RunPrco(string procName, params DbParameter[] ps)
    {
        int num;
        DbHelper Db = new DbHelper();
        DbCommand cmd = Db.GetStoredProcCommand(procName);        

        try
        {
            cmd.Parameters.AddRange(ps);
            cmd.Connection = connection;
            num = Db.ExecuteNonQuery(cmd);            
        }
        catch (Exception e)
        {

            throw e;
        }
        finally
        {
            cmd.Dispose();
        }
        return num;
    }

    public object GetVar(string sql, params DbParameter[] ps)
    {
        object o;
        DbHelper Db = new DbHelper();
        DbCommand cmd = Db.GetSqlStringCommand(sql);

        try
        {
            cmd.Parameters.AddRange(ps);            
            cmd.Connection = connection;
            o = Db.ExecuteScalar(cmd);
            
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            cmd.Dispose();
        }

        return o;          
    }

    public DataRow GetRow(string sql, params DbParameter[] ps)
    {
        DataTable dataTable = Select(sql, ps);
        if (dataTable != null)
        {
            if (dataTable.Rows.Count>0)
            {
                return Select(sql, ps).Rows[0];        
            }
        }

        return null;
    }

    public object Insert(string sql, params DbParameter[] ps)
    {
        return GetVar(sql + ";select SCOPE_IDENTITY();", ps);
    }

    public int Update(string sql, params DbParameter[] ps)
    {
        return RunSql(sql, ps);
    }

    public int Delete(string sql, params DbParameter[] ps)
    {
        return RunSql(sql, ps);
    }

    public DataTable Select(string sql, params DbParameter[] ps)
    {
        DbHelper Db = new DbHelper();
        DbCommand cmd = Db.GetSqlStringCommand(sql);
        DataTable Dt = new DataTable();
        try
        {            
            cmd.Parameters.AddRange(ps);            
            cmd.Connection = connection;
            Dt = Db.ExecuteDataTable(cmd);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            cmd.Dispose();            
        }

        return Dt;
    }

    public DbDataReader ExecuteReader(string sql, params  DbParameter[] ps)
    {   
        DbHelper Db = new DbHelper();
        DbCommand cmd = Db.GetSqlStringCommand(sql);
        try
        {
            cmd.Parameters.AddRange(ps);            
            cmd.Connection = connection;
            return Db.ExecuteReader(cmd);
        }
        catch (Exception e)
        {
            connection.Close();
            throw e;            
        }
    }
}